package dao;

import beans.ClassBean;
import helper.ReadConfig;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MngClass {

    public boolean createClass(ClassBean classBean) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "insert into tbl_Class(Acc_Id, "
                    + " Class_Name, Start_date, Finish_Date, Description) "
                    + " values(" + Integer.parseInt(classBean.getAcc_id()) + ","
                    + "'" + classBean.getClass_name() + "',"
                    + "'" + classBean.getStart_date() + "',"
                    + "'" + classBean.getFinish_date() + "',"
                    + "'" + classBean.getDescription() + "')";
            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateClass(ClassBean classBean) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "Update tbl_Class set "
                    + "   Acc_Id=" + Integer.parseInt(classBean.getAcc_id())
                    + ", Class_Name='" + classBean.getClass_name()
                    + "', Start_date='" + classBean.getStart_date()
                    + "', Finish_Date='" + classBean.getFinish_date()
                    + "', Description='" + classBean.getDescription()
                    + "'  WHERE Class_Id = " + Integer.parseInt(classBean.getClass_id());
            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteClass(String class_id) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "DELETE FROM tbl_Class WHERE Class_Id = " + Integer.parseInt(class_id);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteClassByAccId(String acc_id) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "DELETE FROM tbl_Class WHERE Acc_Id = " + Integer.parseInt(acc_id);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<ClassBean> showClass() {
        try {
            List<ClassBean> listClass = new ArrayList<ClassBean>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Class ";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                ClassBean classBean = new ClassBean();
                classBean.setClass_id(rs.getString("Class_Id"));
                classBean.setAcc_id(rs.getString("Acc_Id"));
                classBean.setClass_name(rs.getString("Class_Name"));
                classBean.setStart_date(rs.getString("Start_Date"));
                classBean.setFinish_date(rs.getString("Finish_Date"));
                classBean.setDescription(rs.getString("Description"));
                listClass.add(classBean);
            }
            rs.close();
            stm.close();
            return listClass;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public String getClassName(String class_id) {
        try {
            String class_name = "";
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Class Where Class_Id = " + Integer.parseInt(class_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                class_name = rs.getString("Class_Name");
            }
            rs.close();
            stm.close();
            return class_name;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public String getAccIdByClassId(String class_id) {
        try {
            String acc_id = "";
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Class Where Class_Id = " + Integer.parseInt(class_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                acc_id = rs.getString("Acc_Id");
            }
            rs.close();
            stm.close();
            return acc_id;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
   
    public boolean checkClassName(String class_name, String class_id) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Class Where Class_Name ='" + class_name
                    + "' and Class_Id != " + Integer.parseInt(class_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                return true;
            }
            rs.close();
            stm.close();
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean checkClassName(String class_name) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Class Where Class_Name ='" + class_name + "'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                return true;
            }
            rs.close();
            stm.close();
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<ClassBean> listClassByAccId(String acc_id) {
        try {
            List<ClassBean> listClass = new ArrayList<ClassBean>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Class where Acc_Id = " + Integer.parseInt(acc_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                ClassBean classBean = new ClassBean();
                classBean.setClass_id(rs.getString("Class_Id"));
                classBean.setAcc_id(rs.getString("Acc_Id"));
                classBean.setClass_name(rs.getString("Class_Name"));
                classBean.setStart_date(rs.getString("Start_Date"));
                classBean.setFinish_date(rs.getString("Finish_Date"));
                classBean.setDescription(rs.getString("Description"));
                listClass.add(classBean);
            }
            rs.close();
            stm.close();
            return listClass;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<ClassBean> listClassByStudentId(String acc_id) {
        try {
            Student st = new Student();
            Student student = new Student();
            List<String> listStr = student.listClassByStudent(acc_id);
            List<ClassBean> listClass = new ArrayList<ClassBean>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            for (String class_id : listStr) {
                String sSQL = "select * from tbl_Class where Class_Id = " + Integer.parseInt(class_id);
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(sSQL);
                if (rs.next()) {
                    ClassBean classBean = new ClassBean();
                    classBean.setClass_id(rs.getString("Class_Id"));
                    classBean.setAcc_id(rs.getString("Acc_Id"));
                    classBean.setClass_name(rs.getString("Class_Name"));
                    classBean.setStart_date(rs.getString("Start_Date"));
                    classBean.setFinish_date(rs.getString("Finish_Date"));
                    classBean.setDescription(rs.getString("Description"));
                    listClass.add(classBean);
                }
                rs.close();
                stm.close();
            }
            return listClass;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
